{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from neo4j import GraphDatabase\n",
    "driver = GraphDatabase.driver('bolt://localhost:7687',auth=('neo4j', 'pleaseletmein'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_query(query):\n",
    "    with driver.session() as session:\n",
    "        session.run(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_constraint_query = \"CREATE CONSTRAINT FOR (m:Movie) REQUIRE m.id IS UNIQUE;\"\n",
    "movie_tag_constraint_query = \"CREATE CONSTRAINT FOR (m:MovieTag) REQUIRE m.id IS UNIQUE;\"\n",
    "run_query(movie_constraint_query)\n",
    "run_query(movie_tag_constraint_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import_query = \"\"\"\n",
    "\n",
    "LOAD CSV FROM \"file:///movie_titles_metadata.tsv\" as row FIELDTERMINATOR \"\\t\"\n",
    "MERGE (m:Movie{id:row[0]})\n",
    "SET m.title = row[1],\n",
    "    m.release_year = toInteger(row[2]),\n",
    "    m.imdb_rating = toFloat(row[3]),\n",
    "    m.no_votes = toInteger(row[4])\n",
    "WITH m, apoc.convert.fromJsonList(\n",
    "          replace(row[5],\" \",\",\")) as tags\n",
    "UNWIND tags as tag\n",
    "MERGE (mt:MovieTag{id:tag})\n",
    "MERGE (m)-[:HAS_TAG]->(mt)\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "run_query(import_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocess attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_string_preprocessing_query = \"\"\"\n",
    "MATCH (m:Movie)\n",
    "SET m.string_rating = toString(toInteger(m.imdb_rating * 10)),\n",
    "    m.string_release_year = toString(m.release_year)\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "run_query(to_string_preprocessing_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "to_range_preprocessing_query = \"\"\"\n",
    "\n",
    "WITH 7 as total_length\n",
    "MATCH (m:Movie)\n",
    "WHERE m.imdb_rating IS NOT NULL\n",
    "WITH m, total_length, \n",
    "        toString(toInteger(m.imdb_rating * 10)) as string_rating\n",
    "WITH m, total_length - size(string_rating) as zeros, string_rating\n",
    "WITH m, apoc.text.join([x in range(1,zeros) | \"0\"],\"\") +    \n",
    "                                  string_rating as final_rating\n",
    "SET m.range_rating = final_rating\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# look at https://brettscott.wordpress.com/2011/11/19/lucene-number-range-search-integers-floats/\n",
    "run_query(to_range_preprocessing_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Full-text index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_fts_index_query = \"\"\"\n",
    "CREATE FULLTEXT INDEX MovieIndex IF NOT EXISTS\n",
    "FOR (m:Movie) ON EACH [m.title, m.string_rating, m.range_rating, m.string_release_year]\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "run_query(create_fts_index_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lucene queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def read_query(query):\n",
    "    with driver.session() as session:\n",
    "        result = session.run(query)\n",
    "        return pd.DataFrame([r.values() for r in result], columns=result.keys())\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# basic\n",
    "basic_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:dream\") YIELD node, score\n",
    "RETURN node.title as title, score\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a nightmare on elm street: the dream child</td>\n",
       "      <td>1.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a nightmare on elm street 3: dream warriors</td>\n",
       "      <td>1.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>1.171073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title     score\n",
       "0     a nightmare on elm street: the dream child  1.270704\n",
       "1    a nightmare on elm street 3: dream warriors  1.270704\n",
       "2  a nightmare on elm street 4: the dream master  1.171073"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(basic_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Logical operator\n",
    "or_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \n",
    "     \"string_release_year:1999 or 2000\") YIELD node, score\n",
    "RETURN node.title as title, score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>american psycho</td>\n",
       "      <td>1.226042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bamboozled</td>\n",
       "      <td>1.226042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>little nicky</td>\n",
       "      <td>1.226042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>wonder boys</td>\n",
       "      <td>1.226042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>scream 3</td>\n",
       "      <td>1.226042</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             title     score\n",
       "0  american psycho  1.226042\n",
       "1       bamboozled  1.226042\n",
       "2     little nicky  1.226042\n",
       "3      wonder boys  1.226042\n",
       "4         scream 3  1.226042"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(or_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wildcard single-character\n",
    "wildcard_single_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:th?\") YIELD node, score\n",
    "RETURN node.title as title, score\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>the fifth element</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>the world is not enough</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>the wizard of oz</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>the witching hour</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wag the dog</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>the avengers</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>airplane ii: the sequel</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>the atomic submarine</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>a nightmare on elm street: the dream child</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>158 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             title  score\n",
       "0                                the fifth element    1.0\n",
       "1                          the world is not enough    1.0\n",
       "2                                 the wizard of oz    1.0\n",
       "3                                the witching hour    1.0\n",
       "4                                      wag the dog    1.0\n",
       "..                                             ...    ...\n",
       "153                                   the avengers    1.0\n",
       "154                        airplane ii: the sequel    1.0\n",
       "155                           the atomic submarine    1.0\n",
       "156     a nightmare on elm street: the dream child    1.0\n",
       "157  a nightmare on elm street 4: the dream master    1.0\n",
       "\n",
       "[158 rows x 2 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(wildcard_single_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wildcard multi-character\n",
    "wildcard_multi_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:drea*\") YIELD node, score\n",
    "RETURN node.title as title, score\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a nightmare on elm street 3: dream warriors</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>my mother dreams the satan's disciples in new ...</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a nightmare on elm street: the dream child</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  score\n",
       "0      a nightmare on elm street 4: the dream master    1.0\n",
       "1        a nightmare on elm street 3: dream warriors    1.0\n",
       "2  my mother dreams the satan's disciples in new ...    1.0\n",
       "3         a nightmare on elm street: the dream child    1.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(wildcard_multi_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fuzzy search\n",
    "fuzzy_search_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:dream~\") YIELD node, score\n",
    "RETURN node.title as title, score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>scream</td>\n",
       "      <td>1.885012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bull durham</td>\n",
       "      <td>1.812557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>point break</td>\n",
       "      <td>1.812557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>scream 3</td>\n",
       "      <td>1.557420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>scream 2</td>\n",
       "      <td>1.557420</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         title     score\n",
       "0       scream  1.885012\n",
       "1  bull durham  1.812557\n",
       "2  point break  1.812557\n",
       "3     scream 3  1.557420\n",
       "4     scream 2  1.557420"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(fuzzy_search_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Range query\n",
    "range_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"string_rating:[50 TO 99}\") YIELD node, score\n",
    "RETURN node.title as title,score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10 things i hate about you</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>zulu dawn</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>young frankenstein</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>x-men</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>xxx</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        title  score\n",
       "0  10 things i hate about you    1.0\n",
       "1                   zulu dawn    1.0\n",
       "2          young frankenstein    1.0\n",
       "3                       x-men    1.0\n",
       "4                         xxx    1.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(range_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Long range query\n",
    "long_range_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"range_rating:[0000050 TO 0000150]\") YIELD node, score\n",
    "RETURN node.title as title,score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10 things i hate about you</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>zulu dawn</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>young frankenstein</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>x-men</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>xxx</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        title  score\n",
       "0  10 things i hate about you    1.0\n",
       "1                   zulu dawn    1.0\n",
       "2          young frankenstein    1.0\n",
       "3                       x-men    1.0\n",
       "4                         xxx    1.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(long_range_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Boosting score\n",
    "boosting_query = \"\"\"\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:dream string_rating:[50 TO 99]^2\") YIELD node, score\n",
    "RETURN node.title as title,score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a nightmare on elm street 3: dream warriors</td>\n",
       "      <td>3.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>3.171073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>young frankenstein</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>x-men</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>xxx</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title     score\n",
       "0    a nightmare on elm street 3: dream warriors  3.270704\n",
       "1  a nightmare on elm street 4: the dream master  3.171073\n",
       "2                             young frankenstein  2.000000\n",
       "3                                          x-men  2.000000\n",
       "4                                            xxx  2.000000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(boosting_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time decay\n",
    "time_decay_query = \"\"\"\n",
    "WITH apoc.text.join([x in range(0,10) | \n",
    "\"string_release_date:\" + toString((date().year - x)) + \"^\" +   \n",
    "  toString(10-x)],\" \") as time_decay\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:dream \" + time_decay) YIELD node, score\n",
    "RETURN node.title as title,score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a nightmare on elm street: the dream child</td>\n",
       "      <td>1.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a nightmare on elm street 3: dream warriors</td>\n",
       "      <td>1.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>1.171073</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title     score\n",
       "0     a nightmare on elm street: the dream child  1.270704\n",
       "1    a nightmare on elm street 3: dream warriors  1.270704\n",
       "2  a nightmare on elm street 4: the dream master  1.171073"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(time_decay_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All together\n",
    "all_together = \"\"\"\n",
    "WITH apoc.text.join([x in range(0,10) | \n",
    "\"string_release_date:\" + toString((date().year - x)) + \"^\" +   \n",
    "  toString(10-x)],\" \") as time_decay\n",
    "CALL db.index.fulltext.queryNodes(\"MovieIndex\", \"title:dream string_rating:[50 TO 99]^2 \"+ time_decay) YIELD node, score\n",
    "// filter only thrillers\n",
    "MATCH (node)-[:HAS_TAG]->(:MovieTag{id:'thriller'})\n",
    "RETURN node.title as title,score\n",
    "LIMIT 5\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a nightmare on elm street 3: dream warriors</td>\n",
       "      <td>3.270704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a nightmare on elm street 4: the dream master</td>\n",
       "      <td>3.171073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>watchmen</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>the world is not enough</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>witness</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title     score\n",
       "0    a nightmare on elm street 3: dream warriors  3.270704\n",
       "1  a nightmare on elm street 4: the dream master  3.171073\n",
       "2                                       watchmen  2.000000\n",
       "3                        the world is not enough  2.000000\n",
       "4                                        witness  2.000000"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_query(all_together)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
